package sopt.dev.db;

import java.util.ArrayList;
import java.util.List;

import sopt.dev.data.CityVO;
import sopt.dev.data.ItemVO;
import sopt.dev.db.CityInfoDB.CityDBScheme;
import sopt.dev.db.ItemInfoDB.ItemDBScheme;
import sopt.dev.util.GagebuApp;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by Dev on 13. 8. 3.
 */

public class DBHelper extends SQLiteOpenHelper implements IDbCRUD {

	private static final String KEY_COLUMN = "_id";
	private static final String DB_NAME = "GagebuDB";
	private static DBHelper mInstance;

	private static SQLiteDatabase mDb;

	private DBHelper() {
		super(GagebuApp.getContext(), DB_NAME, null, 1);
	}

	public static DBHelper getInstance() {
		if (mInstance == null) {
			mInstance = new DBHelper();
			mDb = mInstance.getWritableDatabase();
		}
		return mInstance;
	}

	public void close() {
		if (mInstance != null) {
			mDb.close();
			mInstance = null;
		}
	}


	@Override
	public void onCreate(SQLiteDatabase sqLiteDatabase) {
		createDB(new CityDBCreator(), sqLiteDatabase);
		createDB(new ItemDBCreator(), sqLiteDatabase);
	}
	
	
	private void createDB(IDBCreator dbCreator, SQLiteDatabase db) {
		String[] tableCreateStmt = dbCreator.getCreateTableStmt();

		if (tableCreateStmt != null && tableCreateStmt.length > 0) {
			for (String stmt : tableCreateStmt) {
				db.execSQL(stmt);
			}
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i2) {

	}
	

	@Override
	public Cursor getAllItem(String table, String[] columns) {
		return mDb.query(table, columns, null, null, null, null, null);
	}

	@Override
	public Cursor get(String table, String[] columns, long id) {
		Cursor cursor = mDb.query(true, table, columns, KEY_COLUMN + "=" + id, null, null, null, null, null);

		if (cursor != null)
			cursor.moveToFirst();

		return cursor;
	}

	@Override
	public Cursor get(String sql) {
		return mDb.rawQuery(sql, null);
	}

	@Override
	public long insert(String table, ContentValues values) {
		return mDb.insert(table, null, values);	
	}

	public long replace(String table, String nullColumnHack, ContentValues values) {
		return mDb.replace(table, nullColumnHack, values);
	}
	
	@Override
	public int update(String table, ContentValues values, long id) {
		return mDb.update(table, values, KEY_COLUMN + "=" + id, null);
	}
	

	@Override
	public int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
		return mDb.update(table, values, whereClause, whereArgs);
	}

	@Override
	public int delete(String table, String whereCaluse) {
		return mDb.delete(table, whereCaluse, null);
	}

	@Override
	public int delete(String table, long id) {
		return mDb.delete(table, KEY_COLUMN + "=" + id, null);
	}

	@Override
	public void exec(String sql) {
		mDb.execSQL(sql);
	}

	public List<CityVO<ItemVO>> getAllCityInfo() {

		ArrayList<CityVO<ItemVO>> cityList = new ArrayList<CityVO<ItemVO>>();

		Cursor cursor = getAllItem(CityDBScheme.TABLE_NAME, CityDBScheme.getColumnNames());

		if(cursor.moveToFirst()) {
			do {
				String cityName = cursor.getString(1);
				ArrayList<ItemVO> items = (ArrayList<ItemVO>) getAllItemInfo(cityName);

				/*
				 * CITYDB COLUMN
				 *  0 : id
				 *  1 : name
				 *  2 : in_date
				 *  3 : out_date
				 *  4 : img_path
				 *  5 : currency
				 */
				String inDate = cursor.getString(2);
				String outDate = cursor.getString(3);
				String imgPath = cursor.getString(4);
				int currency = cursor.getInt(5);

				CityVO<ItemVO> city = new CityVO<ItemVO>(imgPath, cityName, currency, inDate, outDate, items );

				cityList.add(city);
				
			} while(cursor.moveToNext());
		}

		return cityList;
	}

	public List<ItemVO> getAllItemInfo(String cityName) {

		List<ItemVO> itemList = new ArrayList<ItemVO>();

		final String comma = ",";

		Cursor cursor = get(
				"SELECT " 
						+ ItemDBScheme.COLUMN_NAME+ comma
						+ ItemDBScheme.COLUMN_DATE+ comma
						+ ItemDBScheme.COLUMN_TYPE  + comma
						+ ItemDBScheme.COLUMN_PRICE + comma
						+ ItemDBScheme.COLUMN_ADDRESS + comma
						+ ItemDBScheme.COLUMN_COMMENT

						+ "FROM " + ItemDBScheme.TABLE_NAME + " I, " + CityDBScheme.TABLE_NAME + " C WHERE " 
						+ " I." + ItemDBScheme.COLUMN_CITYNAME + "=" 
						+ " C." + CityDBScheme.COLUMN_CITYNAME); 

		if(cursor.moveToFirst()) {
			do {
				
				/*
				 * ITEMDB COLUMN
				 *  0 : id
				 *  1 : city_name
				 *  2 : item_name
				 *  3 : item_date
				 *  4 : item_type
				 *  5 : item_price
				 *  6 : item_img_path
				 *  7 : item_address
				 *  8 : item_comment
				 *  9 : is_card
				 */
				String itemName = cursor.getString(2);
				String itemDate = cursor.getString(3);
				int itemType = cursor.getInt(4);
				int itemPrice = cursor.getInt(5);
				String imgPath = cursor.getString(6);
				String address = cursor.getString(7);
				String comment = cursor.getString(8);
				int isCard = cursor.getInt(9);
				
				
				ItemVO item = new ItemVO(cityName, itemName, itemDate, itemType,
						itemPrice, imgPath, address, comment, isCard);
				
				itemList.add(item);				
				
			} while(cursor.moveToNext());
		}

		return itemList;

	}



}
